package com.zhan.ielts.tools;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhan.ielts.domain.CityUniversity;

public class CityUniversityUtil {
	private static Logger log = LoggerFactory.getLogger(CityUniversityUtil.class);
	private static Connection conn = getConnection();

	public static void main(String[] args) {
		System.out.println("--------start");

		String filepath = "/xiaozhan/received files/jiang qian/0330/雅思考场.xlsx";
		importCityUniversity(filepath);

		System.out.println("--------end");

	}

	public static void importCityUniversity(String filePath) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			List<CityUniversity> list = new ArrayList<CityUniversity>();
			int count = 1;
			for (int k = 0; k < 1; k++) {

				XSSFSheet sheet = wb.getSheetAt(k);
				XSSFRow row = null;
				XSSFCell cell = null;

				for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					// for (int i = 0; i < 100; i++) {
					row = sheet.getRow(i);
					if (row.getCell(0) == null) {
						break;
					}
					System.out.println("k->" + k + "--i->" + i);
					String cell0 = "";
					if (row.getCell((short) 0).getCellType() == XSSFCell.CELL_TYPE_STRING) {
						cell0 = String.valueOf(row.getCell((short) 0).getStringCellValue());
					} else {
						cell0 = String.valueOf(row.getCell((short) 0).getRawValue());
					}
					if (StringUtils.isEmpty(cell0) || cell0.equals("null")) {
						break;
					}

					CityUniversity cityUniversity = new CityUniversity();
					cityUniversity.setCityCode(String.valueOf(row.getCell((short) 0)));
					cityUniversity.setPinyin(String.valueOf(row.getCell((short) 1)));
					cityUniversity.setCityName(String.valueOf(row.getCell((short) 2)));
					JSONArray jsonArray = new JSONArray();
					for (int j = 3; j < row.getLastCellNum(); j++) {
						if(StringUtils.isEmpty(String.valueOf(row.getCell((short) j))) || String.valueOf(row.getCell((short) j)).equals("null")){
							break;
						}
						JSONObject jsonObject = new JSONObject();
						jsonObject.element("universityName", String.valueOf(row.getCell((short) j)));
						jsonObject.element("universityCode", String.valueOf(j - 2));
						jsonArray.add(jsonObject);
					}
					cityUniversity.setUniversity(jsonArray.toString());
					list.add(cityUniversity);
				}
			}
			System.out.println(list.toString());
			 updateCityUniversity(list);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void updateCityUniversity(List<CityUniversity> list) throws ClassNotFoundException,
			SQLException {
		if (list == null || list.size() == 0)
			return;

		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		PreparedStatement stmt = conn
				.prepareStatement("replace into t_city_university(city_code, pinyin, city_name,university )VALUES (?, ?, ?, ? )");

		for (int n = 0; n < list.size(); n++) {
			// System.out.println(list.get(n));
			CityUniversity cityUniversity = list.get(n);
			stmt.setString(1, cityUniversity.getCityCode());
			stmt.setString(2, cityUniversity.getPinyin());
			stmt.setString(3, cityUniversity.getCityName());
			stmt.setString(4, cityUniversity.getUniversity());
			stmt.addBatch();
		}

		stmt.executeBatch();
		conn.commit();
	}

	public static Connection getConnection() {
		String className = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		String username = "ieltsdba";
		String userpass = "zaq1_XSW2";
		Connection con = null;
		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
}
